﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Data.OracleClient;
using CMN.Entity;
namespace CMN.DBUtility
{
    /// <summary>
    /// 数据库操作基类(for Oracle)
    /// </summary>
    internal class OracleHelper : IDBHelper
    {
        /// <summary>
        ///  返回DBComd参数
        /// </summary>
        /// <param name="ParaName">参数名</param>
        /// <param name="DataType">参数类型 字符串形式</param>
        /// <returns></returns>
        public DbParameter GetParameter(string ParaName, MyDBType DataType, int size, object value, ParameterDirection direction)
        {
            OracleParameter parameter;
            switch (DataType)
            {
                case MyDBType.Decimal:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.Double);
                    break;
                case MyDBType.Varchar:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.VarChar);
                    break;
                case MyDBType.Nvarchar:
                    parameter = new OracleParameter(ParaName, OracleType.NVarChar);
                    break;
                case MyDBType.DateTime:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.DateTime);
                    break;
                case MyDBType.Image:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.BFile);
                    break;

                case MyDBType.Int:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.Int32);
                    break;
                case MyDBType.Text:
                    parameter=new System.Data.OracleClient.OracleParameter(ParaName, System.Data.OracleClient.OracleType.LongVarChar);
                    break;
                case MyDBType.Long:
                    parameter = new OracleParameter(ParaName, OracleType.Int32);//暂用
                    break;
                case MyDBType.Money:
                    parameter = new OracleParameter(ParaName, OracleType.Number);//暂用
                    break;
                default:
                    throw new ApplicationException("数据类型填写错误！");

            }
            if (size != 0)
            {
                parameter.Size = size;
            }
            parameter.Value = value;
            parameter.Direction = direction;
            return parameter;

        }

        /// <summary>
        /// 获取分页SQL
        /// </summary>
        /// <param name="qp">查询条件类</param>
        /// <returns></returns>
        public string GetPagerSQL(QueryParam qp)
        {
            string strSql =string.Empty;
            if (qp.PageSize == 1 && qp.PageIndex == 1)
            {
                strSql = string.Concat("select ", qp.ReturnFields, " from ", qp.TableName, " where ", qp.Where);
                return strSql;
            }
             strSql = "select X.* from(";

          
            strSql = string.Concat(strSql," select row_number() over(order by ", qp.Orderfld, ") as ROW_NO,", qp.ReturnFields, " from ", qp.TableName);
            if (!string.IsNullOrEmpty(qp.Where))
            {
                strSql = string.Concat(strSql, " where ",qp.Where,") X");
            }
            else
            {
                strSql = string.Concat(strSql, ") X");
            }

            strSql = string.Concat(strSql, " where X.ROW_NO <= ",(qp.PageSize * qp.PageIndex).ToString()," and X.ROW_NO >= ",(qp.PageSize * (qp.PageIndex - 1) + 1).ToString());

            return strSql;
        }

        /// <summary>
        /// 分页获取数据
        /// </summary>
        /// <param name="qp">查询分页类</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public DbDataReader GetPageList(QueryParam qp, string connectionString)
        {
            string sql = GetPagerSQL(qp);
            return ExecuteReader(connectionString, CommandType.Text, sql, null);
        }
        /// <summary>
        /// 得到数据条数
        /// </summary>
        public int GetCount(string connectionString, string tblName, string condition)
        {
            StringBuilder sql = new StringBuilder("select count(*) from " + tblName);
            if (!string.IsNullOrEmpty(condition))
                sql.Append(" where " + condition);

            object count = ExecuteScalar(connectionString, CommandType.Text, sql.ToString(), null);
            return int.Parse(count.ToString());
        }

        /// <summary>
        /// 执行查询，返回DataTable
        /// </summary>
        public DataTable ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                       da.Fill(dt);
                        cmd.Parameters.Clear();
                       return dt;
                    }
                }
            }
        }

        /// <summary>
        /// 在事务中执行查询，返回DataTable
        /// </summary>
        public DataTable ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
           da.Fill(dt);
            cmd.Parameters.Clear();
           return dt;
        }

        /// <summary>
        /// 执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 在事务中执行 Transact-SQL 语句并返回受影响的行数。
        /// </summary>
        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行查询，返回DataReader
        /// </summary>
        public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(connectionString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 在事务中执行查询，返回DataReader
        /// </summary>
        public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 在事务中执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText,
            params DbParameter[] cmdParms)
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 公共查询数据函数Oracle版(非安全函数,传入参数请进行Sql字符串过滤)
        /// </summary>
        /// <param name="pd">委托对象</param>
        /// <param name="pp">查询字符串</param>
        /// <param name="pp">是否返回总数</param>
        /// <param name="RecordCount">返回记录总数</param>
        /// <returns>返回记录集List</returns>
        public  List<T> GetRecordList<T>(PopulateDelegate<T> pd, QueryParam pp,bool isReturnCount, out int RecordCount,string connstring)
        {
            List<T> lst = new List<T>();
            RecordCount = 0;
            string strSql = GetPagerSQL(pp);
            DbDataReader dr = ExecuteReader(connstring, CommandType.Text, strSql, null);
               
              
                Dictionary<string, string> Fileds = new Dictionary<string, string>();
                foreach (DataRow var in dr.GetSchemaTable().Select())
                {
                    // string strTemp = var[0].ToString();
                    Fileds.Add(var[0].ToString(), var[0].ToString());
                }
                while (dr.Read())
                {
                    lst.Add(pd(dr, Fileds));
                }
                dr.Close();

                if (isReturnCount)
                {
                    // 取记录总数
                    string strCmdText = string.Concat("SELECT Count(1) From ", pp.TableName);
                    if (pp.Where != "")
                        strCmdText = string.Concat(strCmdText, " where ", pp.Where);
                    RecordCount = Convert.ToInt32(ExecuteScalar(connstring, CommandType.Text, strCmdText, null));
                }
                dr.Dispose();
               
              
            
            return lst;
        }
        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        public void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType,
            string cmdText, DbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText.Replace("@", ":").Replace("?", ":");

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (OracleParameter parm in cmdParms)
                {
                    parm.ParameterName = parm.ParameterName.Replace("@", ":").Replace("?", ":");
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}
